package com.x.ant.dao;

import com.x.ant.model.Bill;
import com.x.ant.model.Task;
import com.x.framework.dao.BaseDao;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class TaskDao extends BaseDao {

    private final static String selectTaskListSql = "SELECT T1.*, T2.DEPT_NAME, T2.DEPT_LINKMAN_NAME, T2.DEPT_LINKMAN_MOBILE " +
            "FROM ANT_TASK T1, ANT_DEPT T2 WHERE T1.DEPT_ID = T2.DEPT_ID ";
    public List<Task> selectTaskList(Task task) throws Exception{
        StringBuilder sb = new StringBuilder(selectTaskListSql);
        List params = new ArrayList();
        if (this.isString(task.getTaskId())) {
            sb.append("AND T1.TASK_ID = ? ");
            params.add(task.getTaskId());
        }
        if (task.getTaskType() != null) {
            sb.append("AND T1.TASK_TYPE = ? ");
            params.add(task.getTaskType());
        }
        if (this.isString(task.getTaskSenderUserMobile())) {
            sb.append("AND T1.TASK_SENDER_USER_MOBILE = ? ");
            params.add(task.getTaskSenderUserMobile());
        }
        if (task.getTaskState() != null) {
            sb.append("AND T1.TASK_STATE = ? ");
            params.add(task.getTaskState());
        }
        if (task.getTaskPublishState() != null) {
            sb.append("AND T1.TASK_PUBLISH_STATE = ? ");
            params.add(task.getTaskPublishState());
        }
        if (task.getTaskCreateTime() != null) {
            sb.append("AND DATE(T1.TASK_CREATE_TIME) = ? ");
            params.add(task.getTaskCreateTime());
        }
        if (this.isString(task.getTaskLoadingArea())) {
            sb.append("AND T1.TASK_LOADING_AREA = ? ");
            params.add(task.getTaskLoadingArea());
        }
        if (this.isString(task.getTaskUnloadingArea())) {
            sb.append("AND T1.TASK_UNLOADING_AREA = ? ");
            params.add(task.getTaskUnloadingArea());
        }
        if (this.isString(task.getDeptId())) {
            sb.append("AND T1.DEPT_ID = ? ");
            params.add(task.getDeptId());
        }
        if (task.getDept() != null && this.isString(task.getDept().getDeptCode())) {
            sb.append("AND T2.DEPT_CODE LIKE ? ");
            params.add(task.getDept().getDeptCode() + "%");
        }
        sb.append("ORDER BY T1.TASK_STATE, T1.TASK_CREATE_TIME DESC");
        return this.queryForList(sb.toString(), Task.class, params, task);
    }

    private final static String selectTaskNumSql = "SELECT COUNT(1) FROM ANT_TASK T1, ANT_DEPT T2 WHERE T1.DEPT_ID = T2.DEPT_ID ";
    public int selectTaskNum(Task task) throws Exception{
        StringBuilder sb = new StringBuilder(selectTaskNumSql);
        List params = new ArrayList();
        if (this.isString(task.getTaskId())) {
            sb.append("AND T1.TASK_ID = ? ");
            params.add(task.getTaskId());
        }
        if (task.getTaskType() != null) {
            sb.append("AND T1.TASK_TYPE = ? ");
            params.add(task.getTaskType());
        }
        if (this.isString(task.getTaskSenderUserMobile())) {
            sb.append("AND T1.TASK_SENDER_USER_MOBILE = ? " );
            params.add(task.getTaskSenderUserMobile());
        }
        if (task.getTaskState() != null) {
            sb.append("AND T1.TASK_STATE = ? ");
            params.add(task.getTaskState());
        }
        if (task.getTaskPublishState() != null) {
            sb.append("AND T1.TASK_PUBLISH_STATE = ? ");
            params.add(task.getTaskPublishState());
        }
        if (task.getTaskCreateTime() != null) {
            sb.append("AND DATE(T1.TASK_CREATE_TIME) = ? ");
            params.add(task.getTaskCreateTime());
        }
        if (this.isString(task.getTaskLoadingArea())) {
            sb.append("AND T1.TASK_LOADING_AREA = ? ");
            params.add(task.getTaskLoadingArea());
        }
        if (this.isString(task.getTaskUnloadingArea())) {
            sb.append("AND T1.TASK_UNLOADING_AREA = ? ");
            params.add(task.getTaskUnloadingArea());
        }
        if (this.isString(task.getDeptId())) {
            sb.append("AND T1.DEPT_ID = ? ");
            params.add(task.getDeptId());
        }
        if (this.isString(task.getUserId())) {
            sb.append("AND T1.DEPT_ID = ? ");
            params.add(task.getUserId());
        }
        if (task.getDept() != null && this.isString(task.getDept().getDeptCode())) {
            sb.append("AND T2.DEPT_CODE LIKE ? ");
            params.add(task.getDept().getDeptCode() + "%");
        }
        return this.queryForInt(sb.toString(), params);
    }

    private static String updateTaskTruckNumSql = "UPDATE ANT_TASK SET TASK_TRUCK_OCCUPY_NUM = TASK_TRUCK_OCCUPY_NUM + ? WHERE TASK_ID = ?";
    public void updateTaskTruckNum(Task task) {
        Object[] params = {task.getTaskTruckOccupyNum(), task.getTaskId()};
        this.execute(updateTaskTruckNumSql, params);
    }


    private static String selectBillTruckSumSql = "SELECT " +
            "SUM(T2.BILL_TRUCK_LOAD) AS TASK_LOAD_SUM, " +
            "SUM(T2.BILL_TRUCK_LOAD) * T3.TASK_PURCHASE_PRICE AS TASK_PURCHASE_SUM, " +
            "SUM(T2.BILL_TRUCK_LOAD) * T3.TASK_SALE_PRICE AS TASK_SALE_SUM, " +
            "SUM(T2.BILL_TRUCK_LOAD) * T3.TASK_FREIGHT_PRICE AS TASK_FREIGHT_SUM, " +
            "COUNT(T2.BILL_ID) * T3.TASK_COST_PRICE AS TASK_COST_SUM " +
//            "T2.TRUCK_STATE " +
            "FROM " +
            "ANT_BILL T1, " +
            "ANT_BILL_TRUCK T2, " +
            "ANT_TASK T3 " +
            "WHERE T1.TASK_ID = ? " +
            "AND T1.BILL_ID = T2.BILL_ID " +
            "AND T1.TASK_ID = T3.TASK_ID " +
            "AND T2.BILL_TRUCK_STATE = 1 ";
//            "GROUP BY T2.TRUCK_STATE " +
//            "ORDER BY T2.TRUCK_STATE";
    public List<int[]> selectBillTruckSum(Bill bill) {
        Object[] params = {bill.getTaskId()};
        RowMapper<int[]> mapper = new RowMapper<int[]>() {
            public int[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                int[] array = new int[6];
                array[0] = rs.getInt("TASK_LOAD_SUM");
                array[1] = rs.getInt("TASK_PURCHASE_SUM");
                array[2] = rs.getInt("TASK_SALE_SUM");
                array[3] = rs.getInt("TASK_FREIGHT_SUM");
                array[4] = rs.getInt("TASK_COST_SUM");
//                array[5] = rs.getInt("TRUCK_STATE");
                return array;
            }
        };
        return this.queryForList(selectBillTruckSumSql, mapper, params);
    }

}
